/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package researchgrants.parts.GrantRequest.expensePurpose;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import researchgrants.parts.LoggedData.LoggedDataOutSerializable;
import researchgrants.parts.LoggedData.panels.lists.ListableLoggedData;
import researchgrants.parts.db.Db;

/**
 * Represent an ExpensePurpose, which is a field within a GrantRequest.
 * 
 * The class is LoggedDataOutSerializable to allow it to be saved implicitly through panels
 * The class is ListableLoggedData to allow it to populate lists (LoggedDataPanelsList)
 * @author DOStudent1
 */
public class ExpensePurpose implements LoggedDataOutSerializable, ListableLoggedData{
    public static ExpensePurpose getByExpensePurposeName(String expensePurposeName) {
        return(new ExpensePurpose(expensePurposeName));
    }

    // the fields
    private String expensePurposeName;
    private int expensePurposeNumber;
    
    // private constructor
    private ExpensePurpose(String expensePurposeName) {
        this.expensePurposeName=expensePurposeName;
//        syncDataFromDb();
    }

    // populated the data from the database
    // currently is being called upon data request, as oppose to upon creation
    private void syncDataFromDb() {
        try {
            Connection conn = Db.openDbConnection();
            PreparedStatement prepareStatement = Db.createPreparedStatement(conn, "SELECT ExpensePurposeID, ExpensePurposeText FROM tblExpensePurposes WHERE ExpensePurposeText=?");
            prepareStatement.setString(1, expensePurposeName);
            ResultSet expensePurposeResultSet = prepareStatement.executeQuery();
            Db.moveToFirstLine(expensePurposeResultSet);

            expensePurposeNumber=(Integer) Db.getColumn(expensePurposeResultSet, 1);
            expensePurposeResultSet.close();
            prepareStatement.close();
            Db.closeDbConnection(conn);
        } catch (SQLException ex) {
            Logger.getLogger(ExpensePurpose.class.getName()).log(Level.SEVERE, null, ex);
        } catch (Exception ex) {
            Logger.getLogger(ExpensePurpose.class.getName()).log(Level.SEVERE, null, ex);
        }
        
    }
    
    public String getExpensePurposeName() {
//        syncDataFromDb();
        return expensePurposeName;
    }
    
    public int getExpensePurposeId() {
        syncDataFromDb();
        return(expensePurposeNumber);
    }
 

    // Returns a list of all expense purposes
    public static List<ExpensePurpose> getAllExpensePurposes() {
        List<ExpensePurpose> expensePurposeslist = null;
        try {
            expensePurposeslist = new LinkedList<ExpensePurpose>();
            Connection conn = Db.openDbConnection();
            PreparedStatement prepareStatement = Db.createPreparedStatement(conn, "SELECT ExpensePurposeText FROM tblExpensePurposes ORDER BY ExpensePurposeId");
            ResultSet expensePurposesResultSet = prepareStatement.executeQuery();

            while (expensePurposesResultSet.next()) {
                expensePurposeslist.add(ExpensePurpose.getByExpensePurposeName((String) Db.getColumn(expensePurposesResultSet, 1)));
            }

            expensePurposesResultSet.close();
            prepareStatement.close();
            Db.closeDbConnection(conn);
        } catch (SQLException ex) {
            Logger.getLogger(ExpensePurpose.class.getName()).log(Level.SEVERE, null, ex);
        } catch (Exception ex) {
            Logger.getLogger(ExpensePurpose.class.getName()).log(Level.SEVERE, null, ex);
        }
        return expensePurposeslist;
    }

    //////////////////////////////////
    // To allow populating lists
    //////////////////////////////////
    
    @Override
    public String toListId() {
        return(expensePurposeName);
    }

    @Override
    public String toListValue() {
        syncDataFromDb();
        return(expensePurposeName + "(" + Integer.toString(expensePurposeNumber) + ")");
    }
    
    ///////////////////////////////////////////////////////////////////////////////////////////////////////
    // Support saving and loading a AngecyContact through its panels: LoggedDataPanelsListExpnesePurpose
    ///////////////////////////////////////////////////////////////////////////////////////////////////////
    @Override
    public String toLoggedData() {
        return(expensePurposeName);
    }
    
    public static ExpensePurpose fromLoggedData(String loggedData) {
        return(getByExpensePurposeName(loggedData));
    }
}
